﻿Public Class FDITEMCLASS
    Dim connect As CONDB = CONDB.NewConnection()

    Private fodid = Nothing
    Private foodPrdcode = Nothing
    Private food = Nothing
    Private foodStatus = Nothing
    Private masproductSQL = Nothing

    Private selectFditem = "SELECT fd.fodid, fd.food, fd.prdcode, mpd.`prdname`, mpd.`prdcat`, mpc.`catname`, mpd.`ccode`, c1.`name` AS 'cname',  mpd.`rcode`, c2.`name` AS 'rname',mpd.`iccode`, c3.`name` AS 'icname',  mpd.`ircode`, c4.`name` AS 'irname',  mpd.`hccode`, c5.`name` AS 'hcname',  mpd.`hrcode`, c6.`name` AS 'hrname',  mpd.`b_unit`, mu1.unitname_th AS 'buyUnit', mpd.`s_unit`, mu2.unitname_th AS 'sellUnit', mpd.`opdprc`, mpd.`ipdprc`, fd.`status` FROM fditem fd LEFT JOIN masproduct mpd ON fd.prdcode = mpd.prdcode LEFT JOIN masprdcats mpc ON mpd.prdcat = mpc.prdcat LEFT JOIN finchcode c1 ON mpd.ccode = c1.ccode LEFT JOIN finrecode c2 ON mpd.rcode = c2.rcode LEFT JOIN finchcode c3 ON mpd.iccode = c3.ccode LEFT JOIN finrecode c4 ON mpd.ircode = c4.rcode LEFT JOIN finchcode c5 ON mpd.hccode = c5.ccode LEFT JOIN finrecode c6 ON mpd.hrcode = c6.rcode LEFT JOIN masunit mu1 ON mpd.b_unit = mu1.unitid LEFT JOIN masunit mu2 ON mpd.b_unit = mu2.unitid "

    Property selectFditem_ As String
        Get
            Return selectFditem
        End Get
        Set(value As String)
            selectFditem = value
        End Set
    End Property

    Property fodid_ As Integer
        Get
            Return fodid
        End Get
        Set(value As Integer)
            fodid = value
        End Set
    End Property

    Property foodPrdcode_ As Integer
        Get
            Return foodPrdcode
        End Get
        Set(value As Integer)
            foodPrdcode = value
        End Set
    End Property

    Property food_ As String
        Get
            Return food
        End Get
        Set(value As String)
            food = value
        End Set
    End Property

    Property foodStatus_ As Boolean
        Get
            Return foodStatus
        End Get
        Set(value As Boolean)
            If value = True Then
                foodStatus = 1
            Else
                foodStatus = 0
            End If
        End Set
    End Property

    Property masproductSQL_ As String
        Get
            Return masproductSQL
        End Get
        Set(value As String)
            masproductSQL = value
        End Set
    End Property

    Public Function insertFDITEM() As Boolean
        If connect.GetTable("SELECT fodid FROM fditem WHERE fodid = '" & fodid & "'").Rows.Count > 0 Then
            MsgBox("ไม่สามารถเพิ่มรายการอาหารดังกล่าวได้ เนื่องจากมีรายการอาหารดังกล่าวอยู่แล้ว")
            Return False
        Else
            Try
                connect.BeginTrans()

                masproductSQL += " SELECT LAST_INSERT_ID();"

                Dim prdcode As String
                prdcode = connect.ExecuteScalar(masproductSQL)

                Dim sql1 As String = ""
                Dim sql2 As String = ""
                Dim sql3 As String = ""

                If fodid IsNot Nothing Then
                    sql2 += "fodid, "
                    sql3 += "'" & fodid & "', "
                Else
                    sql2 += "fodid, "
                    sql3 += "default, "
                End If

                If prdcode IsNot Nothing Then
                    sql2 += "prdcode, "
                    sql3 += "'" & prdcode & "', "
                Else
                    sql2 += "prdcode, "
                    sql3 += "default, "
                End If

                If food IsNot Nothing Then
                    sql2 += "food, "
                    sql3 += "'" & food & "', "
                Else
                    sql2 += "food, "
                    sql3 += "default, "
                End If


                sql2 += "status "
                sql3 += "'" & foodStatus & "' "

                sql1 += "INSERT INTO fditem (" & sql2 & ") VALUES (" & sql3 & ");"

                connect.ExecuteNonQuery(sql1)
                connect.CommitTrans()
                MsgBox("เพิ่มข้อมูลอาหารสำเร็จแล้ว")
                Return True
            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(ex.ToString)
                Return False
            Finally
                connect.Dispose()
            End Try
        End If
    End Function

    Public Function editFDITEM() As Boolean
        Try
            connect.BeginTrans()

            Dim sql As String = ""
            sql += "UPDATE fditem SET "

            sql += "food = '" & food & "', "
            sql += "status = '" & foodStatus & "' "

            sql += "WHERE fodid = '" & fodid & "'"

            connect.ExecuteNonQuery(sql)
            connect.ExecuteNonQuery(masproductSQL)
            connect.CommitTrans()
            MsgBox("แก้ไขข้อมูลอาหารสำเร็จแล้ว")
            Return True
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox("ไม่สามารถแก้ไขข้อมูลได้" & ex.ToString)
            Return False
        Finally
            connect.Dispose()
        End Try
    End Function

End Class
